
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 04/09/2014 20:39:25
-- Generated from EDMX file: C:\Users\Jesper_Laptop_Lenovo\Desktop\Projekt\Lab3_Cv\GetAJob\CvModel\CvModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [AfDb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ContactPersonWorkAdmin]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactPerson] DROP CONSTRAINT [FK_ContactPersonWorkAdmin];
GO
IF OBJECT_ID(N'[dbo].[FK_WorkApplicationsWorkAd]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkApplications] DROP CONSTRAINT [FK_WorkApplicationsWorkAd];
GO
IF OBJECT_ID(N'[dbo].[FK_WorkApplicationsApplicant]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkApplications] DROP CONSTRAINT [FK_WorkApplicationsApplicant];
GO
IF OBJECT_ID(N'[dbo].[FK_CvExperiences]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Experiences] DROP CONSTRAINT [FK_CvExperiences];
GO
IF OBJECT_ID(N'[dbo].[FK_CvMerits]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Merits] DROP CONSTRAINT [FK_CvMerits];
GO
IF OBJECT_ID(N'[dbo].[FK_CvEducations]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Educations] DROP CONSTRAINT [FK_CvEducations];
GO
IF OBJECT_ID(N'[dbo].[FK_AdminWorkAd]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkAd] DROP CONSTRAINT [FK_AdminWorkAd];
GO
IF OBJECT_ID(N'[dbo].[FK_ApplicantCv]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cv] DROP CONSTRAINT [FK_ApplicantCv];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactPersonWorkAd]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkAd] DROP CONSTRAINT [FK_ContactPersonWorkAd];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[WorkAd]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkAd];
GO
IF OBJECT_ID(N'[dbo].[Admin]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Admin];
GO
IF OBJECT_ID(N'[dbo].[ContactPerson]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContactPerson];
GO
IF OBJECT_ID(N'[dbo].[Applicant]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Applicant];
GO
IF OBJECT_ID(N'[dbo].[Cv]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cv];
GO
IF OBJECT_ID(N'[dbo].[WorkApplications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkApplications];
GO
IF OBJECT_ID(N'[dbo].[Experiences]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Experiences];
GO
IF OBJECT_ID(N'[dbo].[Educations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Educations];
GO
IF OBJECT_ID(N'[dbo].[Merits]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Merits];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'WorkAd'
CREATE TABLE [dbo].[WorkAd] (
    [WorkAdId] int IDENTITY(1,1) NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [DateAdded] datetime  NOT NULL,
    [Proffession] nvarchar(max)  NOT NULL,
    [DayOfApplication] datetime  NOT NULL,
    [WorkDescription] nvarchar(max)  NOT NULL,
    [Qualifications] nvarchar(max)  NOT NULL,
    [Duration] nvarchar(max)  NOT NULL,
    [Salary] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [AdminId] int  NOT NULL,
    [ContactPersonContactPersonId] int  NOT NULL
);
GO

-- Creating table 'Admin'
CREATE TABLE [dbo].[Admin] (
    [AdminId] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [CompanyName] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [Zip] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [AdminTitle] int  NOT NULL
);
GO

-- Creating table 'ContactPerson'
CREATE TABLE [dbo].[ContactPerson] (
    [ContactPersonId] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [WorkAdminId] int  NOT NULL
);
GO

-- Creating table 'Applicant'
CREATE TABLE [dbo].[Applicant] (
    [ApplicantId] int IDENTITY(1,1) NOT NULL,
    [PersonNumber] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [Zip] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [RegistredCv] bit  NOT NULL
);
GO

-- Creating table 'Cv'
CREATE TABLE [dbo].[Cv] (
    [CvId] int IDENTITY(1,1) NOT NULL,
    [Miscellaneous] nvarchar(max)  NOT NULL,
    [Interests] nvarchar(max)  NOT NULL,
    [PersonalQualities] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [ApplicantApplicantId] int  NOT NULL
);
GO

-- Creating table 'WorkApplications'
CREATE TABLE [dbo].[WorkApplications] (
    [WorkApplicationId] int IDENTITY(1,1) NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [WorkAdId] int  NOT NULL,
    [ApplicantId] int  NOT NULL
);
GO

-- Creating table 'Experiences'
CREATE TABLE [dbo].[Experiences] (
    [ExperienceId] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Ranking] int  NOT NULL,
    [CvCvId] int  NOT NULL
);
GO

-- Creating table 'Educations'
CREATE TABLE [dbo].[Educations] (
    [EducationId] int IDENTITY(1,1) NOT NULL,
    [Desription] nvarchar(max)  NOT NULL,
    [CvCvId] int  NOT NULL
);
GO

-- Creating table 'Merits'
CREATE TABLE [dbo].[Merits] (
    [MeritId] int IDENTITY(1,1) NOT NULL,
    [Desription] nvarchar(max)  NOT NULL,
    [Ranking] int  NOT NULL,
    [CvCvId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [WorkAdId] in table 'WorkAd'
ALTER TABLE [dbo].[WorkAd]
ADD CONSTRAINT [PK_WorkAd]
    PRIMARY KEY CLUSTERED ([WorkAdId] ASC);
GO

-- Creating primary key on [AdminId] in table 'Admin'
ALTER TABLE [dbo].[Admin]
ADD CONSTRAINT [PK_Admin]
    PRIMARY KEY CLUSTERED ([AdminId] ASC);
GO

-- Creating primary key on [ContactPersonId] in table 'ContactPerson'
ALTER TABLE [dbo].[ContactPerson]
ADD CONSTRAINT [PK_ContactPerson]
    PRIMARY KEY CLUSTERED ([ContactPersonId] ASC);
GO

-- Creating primary key on [ApplicantId] in table 'Applicant'
ALTER TABLE [dbo].[Applicant]
ADD CONSTRAINT [PK_Applicant]
    PRIMARY KEY CLUSTERED ([ApplicantId] ASC);
GO

-- Creating primary key on [CvId] in table 'Cv'
ALTER TABLE [dbo].[Cv]
ADD CONSTRAINT [PK_Cv]
    PRIMARY KEY CLUSTERED ([CvId] ASC);
GO

-- Creating primary key on [WorkApplicationId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [PK_WorkApplications]
    PRIMARY KEY CLUSTERED ([WorkApplicationId] ASC);
GO

-- Creating primary key on [ExperienceId] in table 'Experiences'
ALTER TABLE [dbo].[Experiences]
ADD CONSTRAINT [PK_Experiences]
    PRIMARY KEY CLUSTERED ([ExperienceId] ASC);
GO

-- Creating primary key on [EducationId] in table 'Educations'
ALTER TABLE [dbo].[Educations]
ADD CONSTRAINT [PK_Educations]
    PRIMARY KEY CLUSTERED ([EducationId] ASC);
GO

-- Creating primary key on [MeritId] in table 'Merits'
ALTER TABLE [dbo].[Merits]
ADD CONSTRAINT [PK_Merits]
    PRIMARY KEY CLUSTERED ([MeritId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [WorkAdminId] in table 'ContactPerson'
ALTER TABLE [dbo].[ContactPerson]
ADD CONSTRAINT [FK_ContactPersonWorkAdmin]
    FOREIGN KEY ([WorkAdminId])
    REFERENCES [dbo].[Admin]
        ([AdminId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContactPersonWorkAdmin'
CREATE INDEX [IX_FK_ContactPersonWorkAdmin]
ON [dbo].[ContactPerson]
    ([WorkAdminId]);
GO

-- Creating foreign key on [WorkAdId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [FK_WorkApplicationsWorkAd]
    FOREIGN KEY ([WorkAdId])
    REFERENCES [dbo].[WorkAd]
        ([WorkAdId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkApplicationsWorkAd'
CREATE INDEX [IX_FK_WorkApplicationsWorkAd]
ON [dbo].[WorkApplications]
    ([WorkAdId]);
GO

-- Creating foreign key on [ApplicantId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [FK_WorkApplicationsApplicant]
    FOREIGN KEY ([ApplicantId])
    REFERENCES [dbo].[Applicant]
        ([ApplicantId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkApplicationsApplicant'
CREATE INDEX [IX_FK_WorkApplicationsApplicant]
ON [dbo].[WorkApplications]
    ([ApplicantId]);
GO

-- Creating foreign key on [CvCvId] in table 'Experiences'
ALTER TABLE [dbo].[Experiences]
ADD CONSTRAINT [FK_CvExperiences]
    FOREIGN KEY ([CvCvId])
    REFERENCES [dbo].[Cv]
        ([CvId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CvExperiences'
CREATE INDEX [IX_FK_CvExperiences]
ON [dbo].[Experiences]
    ([CvCvId]);
GO

-- Creating foreign key on [CvCvId] in table 'Merits'
ALTER TABLE [dbo].[Merits]
ADD CONSTRAINT [FK_CvMerits]
    FOREIGN KEY ([CvCvId])
    REFERENCES [dbo].[Cv]
        ([CvId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CvMerits'
CREATE INDEX [IX_FK_CvMerits]
ON [dbo].[Merits]
    ([CvCvId]);
GO

-- Creating foreign key on [CvCvId] in table 'Educations'
ALTER TABLE [dbo].[Educations]
ADD CONSTRAINT [FK_CvEducations]
    FOREIGN KEY ([CvCvId])
    REFERENCES [dbo].[Cv]
        ([CvId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CvEducations'
CREATE INDEX [IX_FK_CvEducations]
ON [dbo].[Educations]
    ([CvCvId]);
GO

-- Creating foreign key on [AdminId] in table 'WorkAd'
ALTER TABLE [dbo].[WorkAd]
ADD CONSTRAINT [FK_AdminWorkAd]
    FOREIGN KEY ([AdminId])
    REFERENCES [dbo].[Admin]
        ([AdminId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AdminWorkAd'
CREATE INDEX [IX_FK_AdminWorkAd]
ON [dbo].[WorkAd]
    ([AdminId]);
GO

-- Creating foreign key on [ApplicantApplicantId] in table 'Cv'
ALTER TABLE [dbo].[Cv]
ADD CONSTRAINT [FK_ApplicantCv]
    FOREIGN KEY ([ApplicantApplicantId])
    REFERENCES [dbo].[Applicant]
        ([ApplicantId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ApplicantCv'
CREATE INDEX [IX_FK_ApplicantCv]
ON [dbo].[Cv]
    ([ApplicantApplicantId]);
GO

-- Creating foreign key on [ContactPersonContactPersonId] in table 'WorkAd'
ALTER TABLE [dbo].[WorkAd]
ADD CONSTRAINT [FK_ContactPersonWorkAd]
    FOREIGN KEY ([ContactPersonContactPersonId])
    REFERENCES [dbo].[ContactPerson]
        ([ContactPersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContactPersonWorkAd'
CREATE INDEX [IX_FK_ContactPersonWorkAd]
ON [dbo].[WorkAd]
    ([ContactPersonContactPersonId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------